﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SqlImEx.Sql;
using System.IO;
using SqlImEx.Util;
using SqlImEx.Xml;
using System.Text.RegularExpressions;
using System.Data;

namespace SqlImEx
{
    public class Importer
    {
        ISqlContext _context;
        string _path; 
        string _fieldDelimiter;
        string _rowDelimiter;
        string _beginTableDelimiter;
        string _endTableDelimiter;
        string _nullvalue;
        public Importer(ISqlContext context, string path)
        {
            _context = context;
            _context.KeepAlive = true;
            _path = path;
        }

        public Configuration Configuration { get; set; }

        private Action<string> _debugWrite = delegate { };
        public Action<string> DebugWrite
        {
            get { return _debugWrite; }
            set { _debugWrite = value; }
        }

        private bool Include(TableDefinition tbl)
        {
            if (Configuration != null && Configuration.List != null && Configuration.List.Search.Count() > 0)
            {
                var patterns = Configuration.List.Search.Select(x => x.pattern);
                return patterns.Any(p => Regex.IsMatch(tbl.FullSqlName, p));
            }
            return true;
        }

        public void Import()
        {
            var dataSection = false;
            if (File.Exists(_path))
            {
                List<IdentityColumn> identityCols = _context.GetIdentityColumns();

                _context.SetConstraints(false);

                try
                {
                    using (var sr = new StreamReader(_path))
                    {
                        string row;
                        while ((row = sr.ReadLine()) != null)
                        {
                            if (dataSection)
                            {
                                if (row == _beginTableDelimiter)
                                {
                                    row = sr.ReadLine();
                                    string tablename = null;
                                    string schema = null;
                                    var matches = Regex.Matches(row, @"\[[\w\.]+\]").OfType<Match>().Reverse();
                                    foreach (Match match in matches) // in reverse order, the table info will always be first.
                                    {
                                        if (matches.IsFirst(match)) // Table
                                            tablename = match.Value.Replace("[", "").Replace("]", "");
                                        else // schema
                                            schema = match.Value.Replace("[", "").Replace("]", "");
                                    }

                                    TableDefinition tableDef = _context.GetTableData(schema, tablename);
                                    if (Include(tableDef))
                                    {
                                        DebugWrite(tableDef.FullyQualifiedSqlName);
                                        string[] expFields = sr.ReadLine().Split(new[] { _fieldDelimiter }, StringSplitOptions.None);
                                        bool hasIdent = false;
                                        if (identityCols.Any(x => x.Table == tablename && x.Schema == schema))
                                            hasIdent = true;
                                        if (hasIdent)
                                            _context.SetIdentity(schema, tablename, true);
                                        var rows = ReadData(tableDef, sr, expFields);
                                        if (hasIdent)
                                            _context.SetIdentity(schema, tablename, false);
                                        DebugWrite(string.Format(" ({0})\r\n", rows));
                                    }
                                    else
                                        ReadData(tableDef, sr, null, true);
                                }
                            }
                            else
                            {
                                if (row != "[settings]")
                                {
                                    if (row.StartsWith("fd="))
                                        _fieldDelimiter = row.Replace("fd=", "");
                                    if (row.StartsWith("rd="))
                                        _rowDelimiter = row.Replace("rd=", "");
                                    if (row.StartsWith("bt="))
                                        _beginTableDelimiter = row.Replace("bt=", "");
                                    if (row.StartsWith("et="))
                                        _endTableDelimiter = row.Replace("et=", "");
                                    if (row.StartsWith("nv="))
                                        _nullvalue = row.Replace("nv=", "");
                                    if (row == "[data]")
                                        dataSection = true;
                                }
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    _context.SetConstraints(true);
                    throw e;
                }
                _context.SetConstraints(true);
            }
        }
        private int ReadData(TableDefinition tableDef, StreamReader sr, string[] expFields, bool skip = false)
        {
            int rowCount = 0;
            string row;
            if (!skip)
                _context.DeleteAllFrom(tableDef);
            while ((row = FullRow(sr))!=null)
            {
                if (!skip)
                {
                    rowCount++;
                    var filevalues = row.Split(new[] { _fieldDelimiter }, StringSplitOptions.None);

                    var insert = BuildInsert(tableDef, filevalues, expFields);
                    _context.ExecuteSql(insert);
                }
                if (row == _endTableDelimiter)
                    break;
            }
            return rowCount;
        }
        
        private string BuildInsert(TableDefinition tableDef, string[] expValues, string[] expFields)
        {
            var fields = tableDef.Field.Where(x => x.datatype != FieldDataType.timestamp);
            var sb = new StringBuilder();
            sb.AppendLine("insert into " + tableDef.FullyQualifiedSqlName);
            sb.AppendLine("(" + fields.DelimitedString(f => "[" + f.name + "]", ", ") + ")");
            sb.Append("values (");
            foreach (var f in fields)
            {
                int ordinal = Array.IndexOf(expFields.Select(x => x.ToLower()).ToArray(), f.name.ToLower());
                if (ordinal == -1) // defaults here
                {
                    Default dflt = null;
                    string fqcolname = tableDef.FullyQualifiedSqlName + string.Format(".[{0}]", f.name);
                    if (Configuration != null && Configuration.Defaults != null && Configuration.Defaults.Default!=null  && Configuration.Defaults.Default.Length > 0)
                        dflt = Configuration.Defaults.Default.FirstOrDefault(x => Regex.IsMatch(fqcolname, x.col));
                    if (dflt != null)
                    {
                        if (!string.IsNullOrEmpty(dflt.fromcol))
                        {
                            int index = Array.IndexOf(expFields, dflt.fromcol);
                            if (index != -1)
                                sb.Append(DataTypeConversion.ValueToSqlValue(f, expValues[index]));
                        }
                        else if (!string.IsNullOrEmpty(dflt.value))
                            sb.Append(DataTypeConversion.ValueToSqlValue(f, dflt.value));
                    }
                    else if (f.nulls)
                        sb.Append("null");
                    else
                        throw new DataException("cannot find default for " + f.name + " in table: " + tableDef.FullSqlName);
                }
                else
                {
                    if (expValues[ordinal] == _nullvalue)
                        sb.Append("null");
                    else
                        sb.Append(DataTypeConversion.ValueToSqlValue(f, expValues[ordinal]));
                }
                if (Array.IndexOf(tableDef.Field, f) != tableDef.Field.Count() - 1)
                    sb.Append(", ");
            }
            sb.Append(")");
            return sb.ToString();
        }
        private string FullRow(StreamReader sr)
        {
            var sb = new StringBuilder();
            while (true)
            {
                string row = sr.ReadLine();
                sb.Append(row);
                if (row.EndsWith(_rowDelimiter))
                    return sb.ToString().Replace(_rowDelimiter, "");
                if (row == _endTableDelimiter)
                    return null;
            }
        }
    }
}
